<?php

namespace Aoe\Database;

use Exception;
use JetBrains\PhpStorm\ArrayShape;
use PDO;
use Throwable;

/**
 * # Sql语句生成器
 */
class SqlBuilder
{
    /**
     * execute执行方式-插入
     */
    const int FETCH_INSERT = -1;
    /**
     * execute执行方式-创建
     */
    const int FETCH_CREATE = -2;
    
    const string ERR_ID = 'ID参数错误';
    const string ID     = 'id';
    
    /**
     * @var string[] 运行过的SQL
     */
    private array  $sqls = [];
    private Platform $platform;
    
    /**
     * @throws Exception
     */
    public function setPlatform(Platform $platform): self
    {
        $this->platform = $platform;
        return $this;
    }
    
    /**
     * ## 查询
     *
     * @param string   $table
     * @param string[] $columns
     * @param array    $conditions
     * @param ?array   $option
     * @param ?int     $fetch_model
     *
     * @return array
     * @throws Exception
     */
    public function select(
        string $table,
        array  $columns,
        array  $conditions,
        ?array $option = null,
        ?int   $fetch_model = null,
    ): array {
        if ($fetch_model === null) $fetch_model = PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC;
        
        // 默认以ID索引
        if ($fetch_model !== PDO::FETCH_COLUMN) array_unshift($columns, self::ID, self::ID);
        return $this->query($table, $columns, $conditions, $option, $fetch_model);
    }
    
    /**
     * ## 直接构造SQL查询
     *
     * @param string                      $table
     * @param array|string                $columns
     * @param array<Sprite[] | null>|bool $conditions
     * @param array                       $additions
     * @param ?int                        $fetch_model
     *
     * @return array
     * @throws Exception
     */
    public function query(
        string              $table,
        array | string      $columns,
        array | bool | null $conditions,
        array               $additions = [],
        ?int                 $fetch_model = null,
    ): array {
        if ($fetch_model === null) $fetch_model = PDO::FETCH_ASSOC;
        
        $list = $this->platform->mapColumns($columns);
        if (empty($list)) return [];

        [$where, $binding] = $this->_map_where($conditions);
        $options = $this->_map_addition($additions);
        
        $sql = "SELECT $list FROM $table $where $options";
        return $this->execute($sql, $binding, $fetch_model);
    }
    
    /**
     * ## 运行SQL语句
     *
     * @param string    $sql
     * @param ?Sprite[] $binding
     * @param ?int      $fetch_model
     *
     * @return array|int
     * @throws Exception
     */
    public function execute(string $sql, array $binding = [], ?int $fetch_model = null): array | int
    {
        $this->sqls[] = $sql . '; ' . $this->_print_bindings($binding);
        
        $pdo = $this->platform->pdo;
        $stat = $pdo->prepare($sql);
        foreach ($binding as $name => $sprite) $stat->bindValue($name, $sprite->getParam(), $sprite->getBinding());

        $r = $stat->execute();
        
        // 插入操作，返回ID
        if ($fetch_model === self::FETCH_INSERT) return $pdo->lastInsertId();
        // 创建操作
        if ($fetch_model === self::FETCH_CREATE) return $r;
        // 查询操作，返回数据
        if ($fetch_model !== null) return $stat->fetchAll($fetch_model);
        // 删改操作，返回受影响行数
        return $stat->rowCount();
    }
    
    /**
     * ## 根据ID查询
     *
     * @param string       $table
     * @param string|array $columns
     * @param int|int[]    $keys
     * @param array|null   $additions
     *
     * @return array
     * @throws Exception
     */
    public function selectById(string $table, array | string $columns, array | int $keys, ?array $additions = []): array
    {
        // 不查询
        if (empty($columns) || empty($keys)) return [];
        
        if (is_array($columns)) array_unshift($columns, self::ID, self::ID);
        $list = $this->platform->mapColumns($columns);
        if (empty($list)) return [];

        $options = $this->_map_addition($additions);
        $ids = $this->_map_ids($keys);
        
        $sql = "SELECT $list FROM  $table  WHERE $ids $options";
        return $this->execute($sql, [], PDO::FETCH_UNIQUE | PDO::FETCH_ASSOC);
    }
    
    /**
     * 准备ID序列
     *
     * @param $id
     *
     * @return string
     * @throws Exception
     */
    private function _map_ids($id): string
    {
        $id = $this->checkIds($id);
        
        // one
        if (!is_array($id)) return "id = $id";
        // many
        return $this->platform->mapIn('id', $id);
    }
    
    /**
     * @param $ids
     *
     * @return int|int[]
     * @throws Exception
     */
    public function checkIds($ids): int | array
    {
        if (!convert_ids($ids)) throw new Exception(self::ERR_ID);
        
        return $ids;
    }
    
    /**
     * ## 获取ID序列
     *
     * @param string     $table
     * @param Sprite[][] $conditions
     *
     * @return array
     * @throws Exception
     * @noinspection PhpUnused
     */
    public function getIds(string $table, bool | array $conditions): array
    {
        return $this->query(
            $table,
            self::ID,
            $conditions,
            [],
            PDO::FETCH_COLUMN,
        );
    }
    
    /**
     * ## 增加一条记录
     *
     * @param string   $table 表名
     * @param Sprite[] $value
     *
     * @return int
     * @throws Exception
     */
    public function add(string $table, array $value): int
    {
        [$insert, $values, $binds] = $this->_map_insert($value);
        if (empty($insert)) return -1;

        $insert = implode(',', $insert);
        $values = implode(',', $values);

        return $this->execute(
            "INSERT INTO $table($insert)VALUES($values)",
            $binds,
            self::FETCH_INSERT,
        );
    }
    
    /**
     * 生成 插入表达式
     *
     * @param Sprite[] $sprites
     *
     * @return array
     */
    private function _map_insert(array $sprites): array
    {
        /** @var Sprite[] $binds :param[n] = $sprite */
        $binds = [];
        /** @var string[] $insert `field`[] */
        $insert = [];
        // array<$value|:param[n]>
        $values = [];
        
        foreach ($sprites as $sprite) {
            // 跳过置空项
            if (!$sprite) continue;
            
            $mc = $this->_map_column($sprite);
            $insert[] = $mc;

            switch ($sprite->getOperator()) {
                case Operator::RAND_OPERATOR:
                    $values[] = 'RAND()';
                    break;
                case Operator::NOW_OPERATOR:
                    $values[] = time();
                    break;
                default:
                    $param         = ':param' . count($binds);
                    $values[]              = $param;
                    $binds[$param] = $sprite;
                    break;
            }
        }
        
        return [$insert, $values, $binds];
    }
    
    /**
     * ## 删除记录
     *
     * @param string    $table 表名
     * @param int|int[] $keys  ids
     *
     * @return int
     * @throws Exception
     */
    public function delete(string $table, array | int $keys): int
    {
        return $this->execute("DELETE FROM $table WHERE " . $this->_map_ids($keys));
    }
    
    /**
     * ## 修改记录
     *
     * @param string     $table 表名
     * @param Sprite[]   $exp
     *
     * @param ?int|int[] $keys
     *
     * @return int
     * @throws Exception
     */
    public function modify(string $table, array $exp, null | array | int $keys = null): int
    {
        [$list, $binds] = $this->_map_update($exp);
        if (empty($list)) return 0;

        $list = implode(',', $list);
        $ids = $this->_map_ids($keys);
        return $this->execute("UPDATE $table SET $list WHERE $ids", $binds);
    }
    
    /**
     * 生成 更新表达式
     *
     * @param Sprite[] $sprites
     *
     * @return array{string, Sprite[]}
     * @throws Exception
     */
    private function _map_update(array $sprites): array
    {
        /** @var string[] $update expression[] */
        $update = [];
        /** @var Sprite[] $binds */
        $binds = [];
        
        foreach ($sprites as $sprite) {
            // 跳过置空项
            if (!$sprite) continue;
            
            $mc = $this->_map_column($sprite);
            
            switch ($sprite->getOperator()) {
                case Operator::SET_NULL_OPERATOR:
                    $update[] = "$mc = NULL";
                    break;
                case '!':
                    $update[] = "$mc = !$mc";
                    break;
                case '++' :
                    $update[] = "$mc = $mc + 1";
                    break;
                case '--' :
                    $update[] = "$mc = $mc -1";
                    break;
                case Operator::RAND_OPERATOR:
                    $update[] = "$mc = RAND()";
                    break;
                case Operator::NOW_OPERATOR:
                    $update[] = "$mc = TIME()";
                    break;
                case '.':
                    $update[] = $this->platform->mapConcat($mc, $sprite->getParam());
                    break;
                case Operator::REPLACE_OPERATOR:
                    $update[] = $this->platform->mapReplace($mc, $sprite->getParam());
                    break;
                case '!&':
                    $v        = $this->platform->mapOpposite($sprite->getParam());
                    $update[] = "$mc = $mc & $v";
                    break;
                default:
                    $param = ':param' . count($binds);
                    if ($sprite->getOperator() === '=') {
                        $update[] = "$mc = $param";
                    } else {
                        $op       = $sprite->getOperator();
                        $update[] = "$mc = $mc $op $param";
                    }
                    
                    $binds[$param] = $sprite;
                    break;
            }
        }
        
        return [$update, $binds];
    }

    /**
     * 生成 查询表达式
     *
     * @param null | bool|array<Sprite[] | null> $conditions 条件序列
     *
     * @return array
     * @throws Exception
     */
    #[ArrayShape(["string", "array"])]
    private function _map_where(bool | array | string $conditions = false): array
    {
        $binding = [];
        
        if (is_string($conditions)) return [$conditions, $binding];
        // bool的情况
        if (!is_array($conditions)) return [$conditions ? '' : 'WHERE 0', $binding];
        
        // 空数组相当于没有WHERE语句
        if (empty($conditions)) return ['', []];
        
        $WHERE = '';
        $OR    = 'WHERE';
        
        foreach ($conditions as $condition) {
            $str = $this->_map_condition($condition, $binding);
            if (empty($str)) continue;
            
            $WHERE .= "$OR ($str)";
            $OR    = ' OR';
        }
        
        return [$WHERE, $binding];
    }
    
    /**
     * 生成比较项
     *
     * @param Sprite[]|bool $condition 条件序列
     * @param array         $binding   绑定的数据[io]
     *
     * @return string|false
     * @throws Exception
     */
    private function _map_condition(bool | array $condition, array &$binding): string | false
    {
        if (!is_array($condition)) return false;
        
        $expression = '';
        $AND        = '';
        foreach ($condition as $item) {
            // TODO 这种情况应该没有
            if ($item === null) continue;
            
            $str = $this->_map_compare($item, $binding);
            
            if ($str === true) continue;
            if ($str === false) return false;
            
            $expression .= "$AND ($str)";
            $AND        = ' AND';
        }
        return $expression;
    }
    
    /**
     * 生成 比较表达式
     *
     * @param Sprite|bool $sprite  操作精灵对象
     * @param array       $binding 绑定的数据[io]
     *
     * @return string|bool
     * @throws Exception
     */
    private function _map_compare(bool | Sprite $sprite, array &$binding): string | bool
    {
        if (is_bool($sprite)) return $sprite;
        
        $column = $this->_map_column($sprite);
        
        switch ($sprite->getOperator()) {
            case Operator::CARD_BIRTH_LESS:
                return $this->platform->mapCbl($column, $sprite->getParam());
            case Operator::CARD_BIRTH_GREAT:
                return $this->platform->mapCbg($column, $sprite->getParam());
            case Operator::EXPRESSION_OPERATOR :
                return $sprite->getParam();
            case Operator::IN_OPERATOR :
                return $this->platform->mapIn($column, $sprite->getParam(), $sprite->getBinding());
            case Operator::NOT_IN_OPERATOR :
                return $this->platform->mapNotIn($column, $sprite->getParam(), $sprite->getBinding());
            case Operator::IS_NULL_OPERATOR :
                return "$column IS NULL";
            case Operator::NOT_NULL_OPERATOR :
                return "$column IS NOT NULL";
            case Operator::CONTENT_OPERATOR:
                return "($column IS NOT NULL AND $column != '')";
            case Operator::STR_EMPTY_OPERATOR:
                return "($column IS NULL OR $column = '')";
            case Operator::RELATION_OPERATOR:
                return "($column IS NOT NULL AND $column != 0)";
            case Operator::INT_EMPTY_OPERATOR:
                return "($column IS NULL OR $column = 0)";
            case Operator::BETWEEN_OPERATOR :
                return $this->platform->mapBetween($column, $sprite->getParam());
            case Operator::LIKE_OPERATOR :
                return $column . ' LIKE ' . $this->platform->mapLike($sprite->getParam());
            case Operator::NOT_LIKE_OPERATOR :
                return $column . ' NOT LIKE ' . $this->platform->mapLike($sprite->getParam());
            case '&=':
                $v = (int)$sprite->getParam();
                return "$column & $v = $v";
            default:
                $name           = ':param' . count($binding);
                $binding[$name] = $sprite;
                return $column . $sprite->getOperator() . $name;
        }
    }
    
    
    /**
     * 生成 高级查询扩展
     *
     * @param ?array $options
     *
     * @return string
     */
    private function _map_addition(?array $options = []): string
    {
        if (empty($options)) return '';
        
        $group = isset($options['group']) ? ' GROUP BY ' . $this->platform->mapColumn($options['group']) : '';
        $limit = isset($options['limit']) ? $this->_map_limit($options['limit']) : '';
        $order = isset($options['order']) ? $this->_map_order($options['order']) : '';
        
        return "$group $order $limit";
    }
    
    /**
     * LIMIT 语句
     *
     * @param array{int, int}|int|string $limit
     *
     * @return string
     */
    private function _map_limit(array | int | string $limit): string
    {
        if (!is_array($limit)) return ' LIMIT ' . (int)$limit;

        $from = (int)($limit[0]);
        $to   = (int)($limit[1]);
            
        return " LIMIT $from , $to ";
    }
    
    /**
     * ORDER BY 语句
     *
     * @param $order
     *
     * @return string
     */
    private function _map_order($order): string
    {
        /** @var string[] $exps */
        $exps = [];
        foreach ($order as $f => $o) $exps[] = $this->platform->mapColumn($f) . ($o ? 'ASC ' : 'DESC ');

        return ' ORDER BY ' . implode(',', $exps);
    }
    
    /**
     * @param bool $clear
     *
     * @return string[]
     */
    public function getSqls(bool $clear = true): array
    {
        $sqls = $this->sqls;
        if ($clear) $this->sqls = [];
        return $sqls;
    }
    
    
    /**
     * 批量处理
     *
     * @param array<array{ 0: string, 1: Sprite[], 2:int }> $sqls 事务逻辑回调
     *
     * @return bool
     * @noinspection PhpUnused
     */
    public function Transaction(array $sqls): bool
    {
        try {
            $pdo = $this->platform->pdo;
            $pdo->beginTransaction();
            foreach ($sqls as $sql) $this->execute(...$sql);
            $pdo->commit();
            return true;
        } catch (Throwable) {
            $pdo->rollBack();
            return false;
        }
    }
    
    /**
     * @return Platform
     */
    public function getPlatform(): Platform
    {
        return $this->platform;
    }

    /**
     * @param Sprite $sprite
     *
     * @return string
     */
    private function _map_column(Sprite $sprite): string
    {
        return $this->platform->mapColumn($sprite->getColumn());
    }
    
    private function _print_bindings(array $binds): string
    {
        return '[ ' . join(', ', $binds) . ' ]';
    }
    
}